package xupt.se.ttms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import xupt.se.ttms.idao.iCustomerDAO;
import xupt.se.ttms.model.Customer;
import xupt.se.util.MyDBUtil;

public class CustomerDAO implements iCustomerDAO{
	
	
	private final String insertSql="insert into T_Customers(customer_name,customer_sex,customer_bill,customer_tel,customer_count,customer_password,customer_level)values(?,?,?,?,?,?,?)";
	private final String deleteSql="delete from T_Customers where customer_id=?";	
	//通过主键customer_id 删除
	private final String updateSql="update T_Customers set customer_name=?,customer_sex=?,"
			+ "customer_bill=?,customer_tel=?,customer_count=?,customer_password=?,customer_level=? where customer_id=?";
	//通过主键id查询
	private final String selectSqlInt="select * from T_Customers where customer_id=?";
	//通过账号customer_count查询
	private final String selectSqlStr="select * from T_Customers where customer_count=?";
	//查询全部
	private final String selectSqlAll="select * from T_Customers ";
	//查询普通用户
	private final String selectSqlNormal="select * from T_Customers where customer_level=?";
	//查询Vip用户
	private final String selectSqlVip="select * from T_Customers where customer_level=?";
	
	
	@Override
	public boolean execInsert(Customer customer) throws Exception {
		String sql=insertSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			//ps.setInt(1,customer.);
			ps.setString(1,customer.getCustomer_name());
			ps.setString(2,customer.getCustomer_sex());
			ps.setInt(3,customer.getCustomer_bill());
			ps.setString(4,customer.getCustomer_tel());
			ps.setString(5,customer.getCustomer_count());
			ps.setString(6,customer.getCustomer_password());
			ps.setString(7,customer.getCustomer_level());
			flag=ps.execute();
		
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	@Override
	public boolean execUpdate(Customer customer) throws Exception {
		String sql=updateSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			//ps.setInt(1,customer.);
			ps.setString(1,customer.getCustomer_name());
			ps.setString(2,customer.getCustomer_sex());
			ps.setInt(3,customer.getCustomer_bill());
			ps.setString(4,customer.getCustomer_tel());
			ps.setString(5,customer.getCustomer_count());
			ps.setString(6,customer.getCustomer_password());
			ps.setString(7,customer.getCustomer_level());
			
			ps.setInt(8, customer.getCustomer_id());
			
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 查询，传一个类型String,防sql注入攻击
	 * @param sql		sql语句
	 * @param str		where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(String str) throws Exception {
		String sql=selectSqlStr;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, str);
		return rstSet;
	}
	/**
	 * 查询，传一个类型int,防sql注入攻击
	 * @param sql		sql语句
	 * @param i			where条件
	 * @return
	 * @throws Exception
	 */
	public ResultSet execQuery(int i) throws Exception {
		String sql=selectSqlInt;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, i);
		return rstSet;
	}	
	/**
	 * 删除 数据，传一个类型int，防sql注入攻击
	 * @param sql
	 * @param i
	 * @return
	 * @throws Exception
	 */
	public boolean execDelete(int i) throws Exception{
		String sql=deleteSql;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, i);
		return flag;
	}
	@Override
	public ResultSet exexQueryAll() throws Exception {
		String sql=selectSqlAll;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}
	@Override
	public List<Customer> HandleCustomerResultSet(ResultSet rs) throws SQLException {
		 //结果，在第一个查询结果有个rs标记，通过rs.next()遍历获取全部
		 Customer customer=null;
		 
		 List<Customer> customers=new ArrayList<Customer>();
		 //获取结果的数据
       ResultSetMetaData rsmd = rs.getMetaData();
       //获取该条结果有多少个列数据
       int columnCount = rsmd.getColumnCount();
   
	   	System.out.println("获取顾客数据中");
	   	//通过rs.next()遍历获取全部结果
       while (rs.next()) {
       	
       	customer=new Customer();
      	   //输出每条结果的全部数据
          for (int i = 0; i < columnCount; i++) {
       	 
       	   customer.setCustomer_id(rs.getInt("customer_id"));
       	   customer.setCustomer_name(rs.getString("customer_name"));;
       	   customer.setCustomer_sex(rs.getString("customer_sex"));
       	   customer.setCustomer_bill(rs.getInt("customer_bill"));
       	   customer.setCustomer_tel(rs.getString("customer_tel"));
       	   customer.setCustomer_count(rs.getString("customer_count"));
       	   customer.setCustomer_password(rs.getString("customer_password"));
       	   customer.setCustomer_level(rs.getString("customer_level"));
           System.out.print(rs.getString(i+1) + "\t");
          }
          customers.add(customer);
          System.out.println();		                    
       }
       
       return customers;
	}
	@Override
	public ResultSet execQueryNormal(String level) throws Exception {
		String sql=selectSqlNormal;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, level);
		return rstSet;
	}
	@Override
	public ResultSet execQueryVip(String level) throws Exception {
		String sql=selectSqlVip;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, level);
		return rstSet;
	}
	
	
	
	
}
